


package com.primecodegen.common.MainProject;

/**
 *----------------------------------------------------------
 *----------------------------------------------------------
 * @author Mohammad Awwad
 * Version: v0.0
 * contact: mhmdawwad@gmail.com
 *-----------------------------------------------------------
 *-----------------------------------------------------------
 */

import java.sql.*;
import java.util.*;
import com.primecodegen.common.base.*;
import com.primecodegen.util.*;
import java.util.logging.*;

public class MainProjectDAO extends BaseDAO {

       protected static String TblName = "main_project";
       protected java.sql.Connection mainProjectConn;
       protected static final Logger logger = Logger.getLogger (MainProjectDAO.class.getName ());

       public MainProjectDAO () {
              super ();
       }

       public int insert ( IBaseTable mainProjectTable ) throws Exception {


              final boolean isConnSupplied = ( mainProjectConn != null );
              MainProjectTable newRecord = ( MainProjectTable ) mainProjectTable;
              int insertValue = ComConstants.UNREALISTIC_INT;
              Connection con = null;
              PreparedStatement PrepSt = null;
              String sInsertSQL = null;
              StringBuffer sbSQL = new StringBuffer ();

              if ( mainProjectTable == null ) {
                     throw new Exception ("DAOException in MainProjectDAO class :mainProjectTable is Null");
              }
              sInsertSQL = "INSERT INTO " + MainProjectDAO.TblName + " (";
              try {
                     con = isConnSupplied ? mainProjectConn : ResourceManager.getConnection ();

                     String sComma = "";
                     int iColCount = 0;
		

								 if (!util.isEmpty(newRecord.getID())) {
								iColCount = iColCount + 1;
								sbSQL.append(sComma).append(MainProjectDBCols.ID);
								sComma = ",";
									}
					
								 if (!util.isEmpty(newRecord.getName())) {
								iColCount = iColCount + 1;
								sbSQL.append(sComma).append(MainProjectDBCols.NAME);
								sComma = ",";
									}
					 
			
		     sbSQL.append (") VALUES (");
                     sComma = "";
                     for ( int i = 0 ; i < iColCount ; i++ ) {
                            sbSQL.append (sComma).append ("?");
                            sComma = ",";
                     }
                     sbSQL.append (");");

                     sInsertSQL = sInsertSQL + sbSQL.toString ();

                     PrepSt = con.prepareStatement (sInsertSQL ,
                             Statement.RETURN_GENERATED_KEYS);
                     int i = 1;



			 						      if (!util.isEmpty(newRecord.getID())) {
			 							PrepSt.setInt(i++, newRecord.getID());
			 						    }
			 			
			 					   if (!util.isEmpty(newRecord.getName().trim())) {
			 							PrepSt.setString(i++, newRecord.getName().trim());
			 						    }
			 			 

		     insertValue = PrepSt.executeUpdate ();
                     ResultSet rsIDJustInserted = PrepSt.getGeneratedKeys ();
                     if ( rsIDJustInserted.next () ) {
                            insertValue = rsIDJustInserted.getInt (1);
                     }



              } catch ( SQLException sqlEx ) {
                     logger.log (Level.WARNING , "SQLException in MainProjectDAO.insert method Message: {0}" , sqlEx.getMessage ());
                     throw new Exception ("SQLException in MainProjectDAO.insert method");
              } catch ( Exception e ) {
                     logger.log (Level.WARNING, "Exception in MainProjectDAO.insert method Message: {0}", e.getMessage ());
                     throw new Exception ("Exception in MainProjectDAO.insert method");
              } finally {
                     ResourceManager.close (PrepSt);
                     if ( !isConnSupplied ) {
                            ResourceManager.close (con);
                     }
              }

              return insertValue;
       }

       public int deleteByID ( int IDPar ) throws Exception {

              final boolean isConnSupplied = ( mainProjectConn != null );
              int ID = ( int ) IDPar;
              Connection con = null;
              PreparedStatement stmnt = null;
              int noOfRowsDeleted = ComConstants.UNREALISTIC_INT;
              String deleteSQL = "DELETE FROM " + MainProjectDAO.TblName + " WHERE " + MainProjectDBCols.ID + " = ?";
              try {
                     con = isConnSupplied ? mainProjectConn : ResourceManager.getConnection ();
                     stmnt = con.prepareStatement (deleteSQL);
                     stmnt.setInt (1, ID);

                     noOfRowsDeleted = stmnt.executeUpdate ();



              } catch ( SQLException sqlEx ) {
                     logger.log (Level.WARNING, "SQLException in MainProjectDAO.DeleteByID method Message: {0}", sqlEx.getMessage ());
                     throw new Exception ("SQLException in MainProjectDAO.deleteByID method"
                             + sqlEx.getMessage ());
              } catch ( Exception e ) {
                     logger.log (Level.WARNING, "Exception in MainProjectDAO.DeleteByID method Message: {0}", e.getMessage ());
                     throw new Exception ("Exception in MainProjectDAO.deleteByID method"
                             + e.getMessage ());
              } finally {
                     if ( !isConnSupplied ) {
                            ResourceManager.close (con);
                     }
              }
              return noOfRowsDeleted;
       }

       public int updateByCriterion ( IBaseTable mainProjectSrchCriterionDTOPar ,
               IBaseTable updatedRecordPar ) throws Exception {

              final boolean isConnSupplied = ( mainProjectConn != null );
              MainProjectTable mainProjectSrchCriterionDTO = ( MainProjectTable ) mainProjectSrchCriterionDTOPar;
              MainProjectTable updatedRecord = ( MainProjectTable ) updatedRecordPar;
              int iNoOfRecordsUpdated = -1;
              Connection con = null;
              PreparedStatement stmnt = null;
              try {

                     con = isConnSupplied ? mainProjectConn : ResourceManager.getConnection ();
                     StringBuffer sbUpdateSQLStmnt = new StringBuffer ();
                     String sComma = "";
                     sbUpdateSQLStmnt.append ("UPDATE ").append (MainProjectDAO.TblName).append (" SET ");



		 		          if ( !util.isEmpty (updatedRecord.getID ()) ) {
		                             sbUpdateSQLStmnt
		                                     .append (sComma)
		                                     .append (MainProjectDBCols.ID + " = ")
		                                     .append ("?");
		                             sComma = ",";
		                      }
		 		
		 		          if ( !util.isEmpty (updatedRecord.getName ()) ) {
		                             sbUpdateSQLStmnt
		                                     .append (sComma)
		                                     .append (MainProjectDBCols.NAME + " = ")
		                                     .append ("?");
		                             sComma = ",";
		                      }
		 		 
		
                     sbUpdateSQLStmnt.append (getUpdateWhereClause (mainProjectSrchCriterionDTO));

                     stmnt=con.prepareStatement (sbUpdateSQLStmnt.toString ());
		     int i=1;

             		if ( !util.isEmpty (updatedRecord.getID ()) ) {
                                     stmnt.setInt (i++, updatedRecord.getID ());
                                  }
             		
             		if ( !util.isEmpty (updatedRecord.getName ()) ) {
                                     stmnt.setString (i++, updatedRecord.getName ());
                                  }
             		 
                   

                     iNoOfRecordsUpdated = stmnt.executeUpdate ();

              } catch ( Exception e ) {
                     logger.log (Level.WARNING, "Exception in MainProjectDAO.updateByCriterion method Message: {0}", e.getMessage ());
                     throw new Exception (
                             "Exception in MainProjectDAO.updateByCriterion method ");
              } finally {
                     if ( !isConnSupplied ) {
                            ResourceManager.close (con);
                     }
              }

              return iNoOfRecordsUpdated;
       }

      public int updateByID ( IBaseTable newRecordPar ) throws Exception {
              MainProjectTable newRecord = ( MainProjectTable ) newRecordPar;
              if ( newRecord != null ) {
                     MainProjectTable contentsCategorySrchCriterionDTO = new MainProjectTable ();
                     contentsCategorySrchCriterionDTO.setID (newRecord.getID ());
                     return this.updateByCriterion (contentsCategorySrchCriterionDTO , newRecord);
              } else {
                     logger.log (Level.WARNING , "Exception in method MainProjectDAO.update.newRecord passed is null.");
                     throw new Exception (
                             "Exception in method MainProjectDAO.update.newRecord passed is null.");
              }
       }
       private String getUpdateWhereClause ( MainProjectTable mainProjectSrchCriterionDTO ) {
              String sWhere = "";
              if ( mainProjectSrchCriterionDTO.getID () != ComConstants.UNREALISTIC_INT ) {
                     sWhere = " WHERE ID = " + mainProjectSrchCriterionDTO.getID ();
              }
              return sWhere;
       }

 

       public IBaseTable getByID ( int ID ) throws Exception {

              if ( !util.isEmpty (ID) ) {
                     MainProjectTable mainProjectSrchCriterionDTO = new MainProjectTable ();
                     mainProjectSrchCriterionDTO.setID (ID);
                     mainProjectSrchCriterionDTO.setFilter (true);
                     ArrayList<MainProjectTable> mainProjectTables = this.getByCriterionOrderedBy (
                             mainProjectSrchCriterionDTO , null);
                     MainProjectTable mainProjectTable = null;
                     if ( mainProjectTables != null ) {
                            Iterator<MainProjectTable> it = mainProjectTables.iterator ();
                            while ( it.hasNext () ) {
                                   mainProjectTable = ( MainProjectTable ) it.next ();
                            }
                     }

                     return mainProjectTable;
              } else {
                     logger.log (Level.WARNING , "Exception in method MainProjectDAO.getByID.ID passed is unrealistic.");
                     throw new Exception (
                             "Exception in method MainProjectDAO.getByID.ID passed is unrealistic.");
              }
       }



       public ArrayList<MainProjectTable> getByCriterionOrderedBy (
               IBaseTable mainProjectSrchCriterionDTOPar , ColToOrderBy colToOrderBy )
               throws Exception {


              final boolean isConnSupplied = ( mainProjectConn != null );
              MainProjectTable mainProjectSrchCriterionDTO = ( MainProjectTable ) mainProjectSrchCriterionDTOPar;
              Connection con = null;
              PreparedStatement stmnt = null;
              ResultSet rs = null;

              String sWhereClause = "";

              String sOrderBy = "";
              if ( colToOrderBy != null && colToOrderBy.Name != null ) {
                     sOrderBy = this.getOrderBy (colToOrderBy);
              }
              if ( mainProjectSrchCriterionDTO != null && mainProjectSrchCriterionDTO.isFilter ()) {
                     try {
                            sWhereClause = this.getRetrieveWhereClause (mainProjectSrchCriterionDTO);
                     } catch ( Exception e ) {
                            System.out.println ("getByCriterionOrderedBy.Exception..");
                            throw new Exception (" MainProject.getByCriterionOrderedBy.Exception:  "+ e.getMessage ());
                     }

                     if ( sWhereClause != null && !sWhereClause.trim ().equalsIgnoreCase ("") ) {
                            sWhereClause = " WHERE " + sWhereClause;
                     }
              }
              ArrayList<MainProjectTable> mainProjectTables = new ArrayList<MainProjectTable> ();

              try {
                     con = isConnSupplied ? mainProjectConn : ResourceManager.getConnection ();

                     String sSelectSQL = "SELECT * FROM   " + TblName;
//                       StringBuilder sSelectSQL = new StringBuilder();
//                        sSelectSQL.append("SELECT ").append(" roles.ID ").append(" AS ").append(RolesDBCols.ID).append(", roles.Code ").append(" AS ").append(RolesDBCols.CODE).append(", roles.Name ").append(" AS ").append(RolesDBCols.NAME).append(", roles.Description ").append(" AS ").append(RolesDBCols.DESCRIPTION).append(", roles.Details ").append(" AS ").append(RolesDBCols.DETAILS).append(", roles.IsActive ").append(" AS ").append(RolesDBCols.ISACTIVE).append(", roles.IsVisible ").append(" AS ").append(RolesDBCols.ISVISIBLE).append(", roles_group.ID ").append(" AS ").append(RolesDBCols.ROLEGROUPID).append(", roles_group.GroupName ").append(" AS ").append(RolesDBCols.ROLEGROUPNAME).append(" FROM ").append(" roles ").append("INNER JOIN roles_group ON (roles.RolesGroupID = roles_group.ID)");

                     stmnt = con.prepareStatement (sSelectSQL + sWhereClause + sOrderBy);
			

			

                     if ( mainProjectSrchCriterionDTO != null && mainProjectSrchCriterionDTO.isFilter ()) {
                            int colCount = 1;
				

						  if ( !util.isEmpty (mainProjectSrchCriterionDTO.getID ()) ) {
			                                   stmnt.setInt (colCount++ , mainProjectSrchCriterionDTO.getID ());
			                            }
						
						  if ( !util.isEmpty (mainProjectSrchCriterionDTO.getName ()) ) {
			                                   stmnt.setString (colCount++ , mainProjectSrchCriterionDTO.getName ());
			                            }
						 
		  }

                     rs = stmnt.executeQuery ();
                     MainProjectTable mainProjectTable = null;

                     while ( rs.next () ) {
                            mainProjectTable = new MainProjectTable ();


						  
							 mainProjectTable.setID (rs.getInt (MainProjectDBCols.ID));
						
						  
							mainProjectTable.setName (util.removeNullFrom (rs.getString  (MainProjectDBCols.NAME)));
						 
                  
			mainProjectTables.add (mainProjectTable);
                        mainProjectTable = null;
                     }

              } catch ( SQLException sqlEx ) {
                     logger.log (Level.WARNING, "SQLException in MainProjectDAO.insert method Message: {0}", sqlEx.getMessage ());
                     throw new Exception (
                             "SQLException in method:MainProjectDAO.getByCriterionOrderedBy"
                             + sqlEx.getMessage ());
              } catch ( Exception e ) {
                     logger.log (Level.WARNING, "Exception in MainProjectDAO.insert method Message: {0}", e.getMessage ());
                     throw new Exception (
                             "Exception in method:MainProjectDAO.getByCriterionOrderedBy"
                             + e.getMessage ());
              } finally {
                     if ( !isConnSupplied ) {
                            ResourceManager.close (con);
                     }
              }
              return mainProjectTables;
       }

       private String getRetrieveWhereClause ( MainProjectTable mainProjectSrchCriterionDTO )
               throws Exception {


              StringBuffer sbWhereClause = new StringBuffer ();
              String sAnd = "";
              boolean bPrevColExistsInWhereClause = false;
              boolean bAtLeastOneFieldExists = false;

              if ( !util.isEmpty (mainProjectSrchCriterionDTO.getID ()) ) {
                     bAtLeastOneFieldExists = true;
                     sbWhereClause.append (" ID =").append (" ? ");
                     bPrevColExistsInWhereClause = true;
              }

              if ( mainProjectSrchCriterionDTO.Opr != null ) {
		


		                                        if ( !util.isEmpty (mainProjectSrchCriterionDTO.Opr.getIDOpr ().trim ()) && !util.isEmpty (mainProjectSrchCriterionDTO.getID ()) ) {
		                                            bAtLeastOneFieldExists = true;
		                                            if ( bPrevColExistsInWhereClause ) {
		                                                   sAnd = " AND ";
		                                            } else {
		                                                   sAnd = "";
		                                            }
		                                           sbWhereClause.append (sAnd).append (" " + MainProjectDBCols.ID + "  ").append (mainProjectSrchCriterionDTO.Opr.getIDOpr ().trim ()).append ("?");
		                                           bPrevColExistsInWhereClause = true;
		                                     }

				
		                                 if ( !util.isEmpty (mainProjectSrchCriterionDTO.Opr.getNameOpr ().trim ()) && !util.isEmpty (mainProjectSrchCriterionDTO.getName ().trim ()) ) {
		                                            bAtLeastOneFieldExists = true;
		                                            if ( bPrevColExistsInWhereClause ) {
		                                                   sAnd = " AND ";
		                                            } else {
		                                                   sAnd = "";
		                                            }
		                                            if ( mainProjectSrchCriterionDTO.Opr.getNameOpr ().trim ().equalsIgnoreCase ("LIKE") ) {
		                                                   sbWhereClause.append (sAnd).append (" " + MainProjectDBCols.NAME + " LIKE").append (" ? ");
		                                            } else {
		                                                   sbWhereClause.append (sAnd).append (" " + MainProjectDBCols.NAME + "  ").append (mainProjectSrchCriterionDTO.Opr.getNameOpr ().trim ()).append ("?");
		                                            }
		                                            bPrevColExistsInWhereClause = true;
		                                     }

				


              }

              if ( sbWhereClause.toString ().trim ().length () <= 0 ) {
                     logger.log (Level.WARNING , "getRetrieveWhereClause Cannot return Null  ");
                     throw new Exception ("getRetrieveWhereClause Cannot return Null  ");
              } else {
                     return sbWhereClause.toString ().trim ();
              }
       }

       private String getOrderBy ( ColToOrderBy colToOrderBy ) {
              String sOrderBy = " Order By ";

              sOrderBy = sOrderBy + colToOrderBy.Name;

              if ( colToOrderBy.OrderType != null ) {
                     sOrderBy = sOrderBy + " " + colToOrderBy.OrderType;
              }
              return sOrderBy;
       }

       public int deleteByCriterion ( IBaseTable recordsToDeleteCritPar )
               throws Exception {

              final boolean isConnSupplied = ( mainProjectConn != null );
              MainProjectTable recordsToDeleteCrit = ( MainProjectTable ) recordsToDeleteCritPar;
              int iNoOfRecordsDeleted = -1;
              Connection con = null;
              PreparedStatement stmnt = null;

              StringBuffer sbDeleteSQLStmnt = new StringBuffer ();
              sbDeleteSQLStmnt
                      .append ("DELETE from ")
                      .append (MainProjectDAO.TblName)
                      .append (" WHERE ");

              String sWhereClause = "";
              if ( recordsToDeleteCrit != null &&  recordsToDeleteCrit.isFilter ()) {
                     try {
                            sWhereClause = this.getRetrieveWhereClause (recordsToDeleteCrit);
                     } catch ( Exception e ) {
                            System.out.println ("getByCriterionOrderedBy.Exception..");
                            throw new Exception (" MainProject.deleteByCriterion.Exception:  "
                                    + e.getMessage ());
                     }

                     if ( sWhereClause != null
                             && !sWhereClause.trim ().equalsIgnoreCase ("") ) {
                            sWhereClause = " WHERE " + sWhereClause;
                     }

                     sbDeleteSQLStmnt.append (sWhereClause);
              }
              try {
                     con = isConnSupplied ? mainProjectConn : ResourceManager.getConnection ();
                     stmnt = con.prepareStatement (sbDeleteSQLStmnt.toString ());

                     if ( recordsToDeleteCrit != null &&  recordsToDeleteCrit.isFilter ()) {

                     }
                   //  iNoOfRecordsDeleted = stmnt.executeUpdate ();



                     return iNoOfRecordsDeleted;
              } catch ( SQLException sqlEx ) {
                     logger.log (Level.WARNING, "SQLException in MainProjectDAO.deleteByCriterion method Message: {0}", sqlEx.getMessage ());
                     throw new Exception (
                             "SQLException in MainProjectDAO.deleteByCriterion method");
              } catch ( Exception e ) {
                     logger.log (Level.WARNING, "Exception in MainProjectDAO.deleteByCriterion method Message: {0}", e.getMessage ());
                     throw new Exception (
                             "Exception in MainProjectDAO.deleteByCriterion method");
              } finally {

                     if ( !isConnSupplied ) {
                            ResourceManager.close (con);
                     }
              }
       }
}
